import pandas as pd
import pymysql

from flask import Flask, render_template, request

app = Flask(__name__)

db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
cursor = db.cursor()


# sql = 'select * from side_table where type = "点位"'

def select_all_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        return result
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


@app.route('/')
def index():
    return 'Hello World'


@app.route('/look', methods=['POST', 'GET'])
def look():
    if request.method == "POST":
        print(request.form.get('type'))
        if request.form.get('type') == 'a':
            project = request.form.get('project')
            print(project)
            da = {}
            site_id = select_all_mysql('select distinct site from main_table where project = "%s"' % (project))
            month_id = select_all_mysql('select distinct month from main_table where project = "%s"' % (project))
            for month in month_id:
                a = {}

                for site in site_id:
                    score = select_all_mysql(
                        'select count(*) from main_table where quantitative > "0.0" and project = "%s" and site = "%s" and month = "%s"' % (
                            project, site[0], month[0]))
                    if score == ():
                        a[site[0]] = "0"
                        da[month[0]] = a
                    else:
                        a[site[0]] = score[0][0]
                        da[month[0]] = a
            f = {}
            for site in site_id:
                count = select_all_mysql(
                    'select count(*) from main_table where quantitative > "0.0" and project = "%s" and site = "%s"' % (
                        project,
                        site[0]))
                f[site[0]] = count[0][0]
            da['总表'] = f

            print(da)

            return da
        else:
            project = request.form.get('project')
            ph_name = select_all_mysql(
                'select distinct ph_name from physics_value_table where project="%s"' % (project))
            da = {}
            for i in ph_name:
                daa = {}
                site_num = select_all_mysql(
                    'select site,num from physics_value_table where project="%s" and ph_name = "%s"' % (project, i[0]))

                for t in site_num:
                    daa[t[0]] = t[1]
                da[i[0]] = daa
                print(da)
            return da
    else:
        print('sb')

    return render_template('table_01.html')
